﻿using System.Text;
using BaseData.CommonLib.Models;
using Dapper;
using Microsoft.Data.SqlClient;
using Swimj.CommonLib.Base;
using Swimj.DataCore;

namespace BaseData.ApplicationLib.Queries;

public class SaleOrderQueries : ISaleOrderQueries
{
    private string? _defaultConnStr;

    public SaleOrderQueries(string? defaultConnStr)
    {
        this._defaultConnStr = defaultConnStr;
    }
    
    public async Task<PaginatedResult<SaleOrderInfoResponseDto>?> QuerySaleOrdersByPage(SaleOrderSearchRequestDto model)
    {
          IEnumerable<SaleOrderInfoResponseDto>? data;
            int totalCount;
            using (var db = new SqlConnection(_defaultConnStr))
            {
                var query = new Query(db);
                StringBuilder sqlBuilder = new StringBuilder(@"
select id,
       code,
       name,
       customerid,
       customerordercode,
       contractordercode,
       deliverytime,
       saleordertype,
       saleorderstatus,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_SALEORDER where 1 = 1 ");

                DynamicParameters parameters = new();

                if (!string.IsNullOrEmpty(model.Code))
                {
                    sqlBuilder.Append(" and code like @Code ");
                    parameters.Add("Code", $"%{model.Code}%");
                }

                if (!string.IsNullOrEmpty(model.Name))
                {
                    sqlBuilder.Append(" and name like @Name");
                    parameters.Add("Name", $"%{model.Name}%");
                }

                query.BuildPageQueries(model.Page, model.Limit, sqlBuilder.ToString());

                data = await db.QueryAsync<SaleOrderInfoResponseDto>(query.SqlPage, parameters);
              
                totalCount = await db.ExecuteScalarAsync<int>(query.SqCount, parameters);
            }

            return await PaginatedResult<SaleOrderInfoResponseDto>.SuccessAsync(data.ToList(), totalCount,
                model.Page,
                model.Limit);
    }

    public async Task<Result<IList<SaleOrderInfoResponseDto>>?> QuerySaleOrders(SaleOrderSearchRequestDto model)
    {
        
        IEnumerable<SaleOrderInfoResponseDto>? data;
        using (var db = new SqlConnection(_defaultConnStr))
        {
            StringBuilder sqlBuilder = new StringBuilder(@"
select id,
       code,
       name,
       customerid,
       customerordercode,
       contractordercode,
       deliverytime,
       saleordertype,
       saleorderstatus,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_SALEORDER where 1 = 1 ");

            DynamicParameters parameters = new DynamicParameters();

            data = await db.QueryAsync<SaleOrderInfoResponseDto>(sqlBuilder.ToString(), parameters);
        }

        return await Result<IList<SaleOrderInfoResponseDto>>.SuccessAsync(data.ToList());
    }

    public async Task<Result<SaleOrderInfoResponseDto>?> GetSaleOrder(int id)
    {
        SaleOrderInfoResponseDto? data;
        using (var db = new SqlConnection(_defaultConnStr))
        {
            StringBuilder sqlBuilder = new StringBuilder(@"
select id,
       code,
       name,
       customerid,
       customerordercode,
       contractordercode,
       deliverytime,
       saleordertype,
       saleorderstatus,
       description,
       datastate,
       createdby,
       createdon,
       lastmodifiedby,
       lastmodifiedon,
       deletedby,
       deletedon
from MES_SALEORDER where 1 = 1  and id = @Id");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("Id", id);
            data = await db.QueryFirstOrDefaultAsync<SaleOrderInfoResponseDto>(sqlBuilder.ToString(), parameters);
        }

        return await Result<SaleOrderInfoResponseDto>.SuccessAsync(data);
    }
}